﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using TracNghiemTinHocDaiCuong.Common;
using TracNghiemTinHocDaiCuong.ThucThe;

namespace TracNghiemTinHocDaiCuong.DAO
{
    public class DeThiDAO
    {
        public static IList<DeThi> TimKiem(string tenDT, string range)
        {
            string query = string.Format("SELECT * " +
                                            "FROM DeThi ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", tenDT)
            };
            IList<DeThi> danhSachDeThi = new List<DeThi>();
            if (tenDT != "")
            {
                query += string.Format("WHERE TenDT LIKE '%'+@TenDT+'%' " +
                " ORDER BY TenDT");
                return DanhSachDeThi(danhSachDeThi, query, parameter);
            }
            else
            {
                query += string.Format("WHERE TenDT LIKE @Range " +
                " ORDER BY TenDT");
                var par = new Collection<SqlParameter>
                {
                    new SqlParameter("@Range", range)
                };
                return DanhSachDeThi(danhSachDeThi, query, par);
            }
        }

        public static IList<DeThi> DanhSachDeThi(IList<DeThi> danhSachDeThi,
            string query, Collection<SqlParameter> parameter)
        {
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            while (read.Read())
            {
                danhSachDeThi.Add(DoiTuong(read));
            }
            return danhSachDeThi;
        }

        public static DeThi TimTheoId(int idDeThi)
        {
            string query = string.Format("SELECT * FROM DeThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", idDeThi)
            };
            IDataReader read = SqlHelper.ExecuteReader(query, parameter);
            DeThi DeThi = new DeThi();
            while (read.Read())
            {
                DeThi = DoiTuong(read);
            }
            return DeThi;
        }

        public static DeThi DoiTuong(IDataRecord read)
        {
            return new DeThi
            {
                MaDT = Methods.ConvertNumber(read["MaDT"].ToString()),
                TenDT = Methods.ConvertString(read["TenDT"].ToString()),
                MaCD = Methods.ConvertNumber(read["MaCD"].ToString()),
                DoKho = Methods.ConvertString(read["DoKho"].ToString()),
                SoCauHoi = Methods.ConvertNumber(read["SoCauHoi"].ToString()),
                ThoiGian = Methods.ConvertNumber(read["ThoiGian"].ToString()),
                NgayTao = Methods.ConvertDateTime(read["NgayTao"].ToString()),
                DaThi = Methods.ConvertBoolean(read["DaThi"].ToString())
            };
        }

        public static IList<DeThi> LayDanhSach()
        {
            IList<DeThi> danhSach = new List<DeThi>();
            string query = string.Format("SELECT * FROM DeThi");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while(read.Read())
            {
                danhSach.Add(DoiTuong(read));
            }
            return danhSach;
        }
        /// <summary>
        /// Phương thức Thêm đề thi trong query không có trường DaThi (đã thi hay chưa) vì để mặc định 
        /// giá trị của DaThi=false
        /// </summary>
        /// <param name="deThi"></param>
        /// <returns></returns>
        public static bool Them(DeThi deThi)
        {
            string query = string.Format("INSERT INTO DeThi(TenDT, MaCD, DoKho, SoCauHoi, ThoiGian, NgayTao, DaThi) " +
            "VALUES(@TenDT, @MaCD, @DoKho, @SoCauHoi, @ThoiGian, @NgayTao, @DaThi)");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@MaCD", deThi.MaCD),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@NgayTao", deThi.NgayTao),
                new SqlParameter("@DaThi", deThi.DaThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Sua(DeThi deThi)
        {
            string query = string.Format("UPDATE DeThi SET TenDT=@TenDT, MaCD=@MaCD, DoKho=@DoKho, " +
            "SoCauHoi=@SoCauHoi, ThoiGian=@ThoiGian, DaThi=@DaThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", deThi.MaDT),
                new SqlParameter("@TenDT", deThi.TenDT),
                new SqlParameter("@MaCD", deThi.MaCD),
                new SqlParameter("@DoKho", deThi.DoKho),
                new SqlParameter("@SoCauHoi", deThi.SoCauHoi),
                new SqlParameter("@ThoiGian", deThi.ThoiGian),
                new SqlParameter("@DaThi", deThi.DaThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Xoa(int idDeThi)
        {
            string query = string.Format("DELETE FROM DeThi WHERE MaDT=@MaDT");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaDT", idDeThi)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}